package com.supinfo.bonjoursupinfo.database;

import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.supinfo.bonjoursupinfo.activities.MainTabFragmentActivity;
import com.supinfo.bonjoursupinfo.models.Picture;

public class PictureBDD {
	
	public static final String TABLE_NAME = "pictures";
	public static final String COL_ID = "_id";
	public static final int NUM_COL_ID = 0;
	public static final String COL_NAME = "name";
	public static final int NUM_COL_NAME = 1;
	public static final String COL_DESC = "description";
	public static final int NUM_COL_DESC= 2;
	public static final String COL_URL = "url";
	public static final int NUM_COL_URL = 3;
	public static final String COL_CREATIONDATE = "creationDate";
	public static final int NUM_COL_CREATIONDATE = 4;
	public static final String COL_ISVALID = "isValid";
	public static final int NUM_COL_ISVALID = 5;
 
	public SQLiteDatabase bdd;
 
	private Database Database = MainTabFragmentActivity.database; 
	
	public void open(){
		bdd = Database.getWritableDatabase();
	}
 
	public void close(){
		bdd.close();
	}
 
	public SQLiteDatabase getBDD(){
		return bdd;
	}
	
	public long insertPicture(Picture picture){
		Picture u = getPictureWithId(picture.getId());
		if(u == null){
			return bdd.insert(TABLE_NAME, null, putValues(picture));
		}else{
			return updatePicture(picture.getId(), picture);
		}
	}
	
	public int updatePicture(long id, Picture picture){
		return bdd.update(TABLE_NAME, putValues(picture), COL_ID + " = " +id, null);
	}
	
	public int removeBase(){
		return bdd.delete(TABLE_NAME, "1", null);
	}
	
	public int removePictureWithID(long id){
		return bdd.delete(TABLE_NAME, COL_ID + " = " +id, null);
	}
	
//	public ArrayList<Picture> getAllPictures() {
//		Cursor c = bdd.query(TABLE_NAME, new String[] 
//				{COL_ID, COL_NAME, COL_DESC, COL_URL, COL_CREATIONDATE, COL_ISVALID},
//				null, null, null, null, COL_CREATIONDATE+" ASC");
//		return cursorToPictures(c);
//	}
	
	public Cursor getAllPictures() {
		Cursor c = bdd.query(TABLE_NAME, new String[] 
				{COL_ID, COL_NAME, COL_DESC, COL_URL, COL_CREATIONDATE, COL_ISVALID},
				null, null, null, null, COL_CREATIONDATE+" DESC");
		return c;
	}
	
	public Picture getPictureWithName(String name){
		Cursor c = bdd.query(TABLE_NAME, new String[] 
				{COL_ID, COL_NAME, COL_DESC, COL_URL, COL_CREATIONDATE, COL_ISVALID},
				COL_NAME + " LIKE \"" + name +"\"", null, null, null, COL_CREATIONDATE +" DESC");
		return cursorToPicture(c);
	}
	public Picture getPictureWithId(long id){
		Cursor c = bdd.query(TABLE_NAME, new String[] 
				{COL_ID, COL_NAME, COL_DESC, COL_URL, COL_CREATIONDATE, COL_ISVALID}, 
				COL_ID + " LIKE \"" + id +"\"", null, null, null, COL_CREATIONDATE +" DESC");
		return cursorToPicture(c);
	}

	public Picture getPictureWithTitle(String news){
		Cursor c = bdd.query(TABLE_NAME, new String[] 
				{COL_ID, COL_NAME, COL_DESC, COL_URL, COL_CREATIONDATE, COL_ISVALID}, 
				COL_CREATIONDATE + " LIKE \"" + news +"\"", null, null, null, COL_CREATIONDATE +" DESC");
		Picture u = cursorToPicture(c);
		return u;
	}
	private Picture cursorToPicture (Cursor c){
		if(c.isClosed()){
			return null;
		}
		if (c.getCount() == 0)return null;
		c.moveToFirst();
		Picture picture= new Picture();
		picture.setId(c.getLong(NUM_COL_ID));
		picture.setName(c.getString(NUM_COL_NAME));
		picture.setDescription(c.getString(NUM_COL_DESC));
		picture.setUrl(c.getString(NUM_COL_URL));
		picture.setCreationDate(new Date(c.getLong(NUM_COL_CREATIONDATE)));
		picture.setIsValid(c.getInt(NUM_COL_ISVALID)>0);
		c.close();
		return picture;
	}
	
	@SuppressWarnings("unused")
	private ArrayList<Picture> cursorToPictures(Cursor c){
		ArrayList<Picture> all = new ArrayList<Picture>();
		if(c.moveToFirst()){
			do {
				Picture picture= new Picture();
				picture.setId(c.getLong(NUM_COL_ID));
				picture.setName(c.getString(NUM_COL_NAME));
				picture.setDescription(c.getString(NUM_COL_DESC));
				picture.setUrl(c.getString(NUM_COL_URL));
				picture.setCreationDate(new Date(c.getLong(NUM_COL_CREATIONDATE)));
				picture.setIsValid(c.getInt(NUM_COL_ISVALID)>0);
				all.add(picture);
			} while (c.moveToNext());
		}
		return all;
	}
	
	private ContentValues putValues(Picture picture){
		ContentValues values = new ContentValues();	
		values.put(COL_ID, picture.getId());	
		values.put(COL_NAME, picture.getName());
		values.put(COL_DESC, picture.getDescription());
		values.put(COL_URL, picture.getUrl());
		values.put(COL_CREATIONDATE, (long) picture.getCreationDate().getTime());
		values.put(COL_ISVALID, picture.getIsValid());
		return values;
	}
}
